#!/use/bin/python
# coding=utf-8
# 主动服务 介绍页/设置页转化率
import datetime
import pymysql
from datetime import date, timedelta

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 主动服务-介绍页/设置页转化率 analysis_conversion_rate
# serId 服务
def insertConversionRate(date,serId):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当天 早间开机转化率的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_conversion_rate t where 1=1 and t.service_key ='{serId}'  and t.ref_date =  '{date}'")
        # 获取当日进入介绍页次数 且早间服务开关为关闭的
        introduceNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}' and  JSON_EXTRACT(t.msg,'$.{serId.lower()}') =0 and JSON_EXTRACT(t.msg,'$.scene') ='{serId.upper()}'")
        # 获取当日从介绍页打开早间服务的次数
        introduceOpenNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'openServer2' and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}' and JSON_EXTRACT(t.msg,'$.serId') = '{serId.upper()}' and JSON_EXTRACT(t.msg,'$.from') = 'introductionPage'")
        # 获取当日进入设置次数 且早间服务开关为关闭的
        settingNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'settingPageShow2' and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}' and  JSON_EXTRACT(t.msg,'$.{serId.lower()}') =0")
        # 获取当日从设置页打开早间服务的次数
        settingOpenNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'openServer2' and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}' and JSON_EXTRACT(t.msg,'$.serId') = '{serId.upper()}' and JSON_EXTRACT(t.msg,'$.from') = 'setting'")
        introduceOpenRate = getRateByXY(introduceOpenNum, introduceNum)
        settingOpenRate = getRateByXY(settingOpenNum, settingNum)
        serName = serIdToName(serId)
        if countNum == 0:
            logDetail = f"{date}日新增-{serName}-进入介绍页次数{introduceNum},进入介绍页并打开服务次数{introduceOpenNum},介绍页转化率{introduceOpenRate}," \
                        f"进入设置页次数{settingNum},进入设置页并打开服务次数{settingOpenNum},设置页转化率{settingOpenRate}"
            print(logDetail)
            # 插入早间服务转化率
            insertSql = f"""insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value({introduceNum},{introduceOpenNum},{introduceOpenRate},{settingNum},{settingOpenNum},{settingOpenRate},'{serId.upper()}','{date}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-{serName}-进入介绍页次数{introduceNum},进入介绍页并打开服务次数{introduceOpenNum},介绍页转化率{introduceOpenRate}," \
                        f"进入设置页次数{settingNum},进入设置页并打开服务次数{settingOpenNum},设置页转化率{settingOpenRate}"
            print(logDetail)
            # 更新早间服务订阅设备量
            updateSql = f"""
                update analysis_conversion_rate set introduction_show_num = {introduceNum} ,introduction_open_num = {introduceOpenNum} ,introduction_conver_rate = {introduceOpenRate} 
                ,setting_show_num = {settingNum} ,setting_open_num = {settingOpenNum} ,setting_conver_rate = {settingOpenRate} ,update_time = now()  where
                    ref_date = '{date}' and service_key ='{serId.upper()}' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 主动服务-介绍页/设置页转化率 analysis_conversion_rate -- 当月
def insertConversionRateMonth(toMonthDay,serId):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当月 早间开机转化率的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_conversion_rate t where 1=1 and t.service_key ='{serId}'  and t.ref_date =  '{toMonthDay}'")
        # 获取当月进入介绍页次数 且早间服务开关为关闭的
        introduceNum = selectNumBySql(
            f"select sum(introduction_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10 and t.service_key ='{serId}'")
        # 获取当月从介绍页打开早间服务的次数
        introduceOpenNum = selectNumBySql(
            f"select sum(introduction_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10 and t.service_key ='{serId}'")
        # 获取当月进入设置次数 且早间服务开关为关闭的
        settingNum = selectNumBySql(
            f"select sum(setting_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10 and t.service_key ='{serId}'")
        # 获取当月从设置页打开早间服务的次数
        settingOpenNum = selectNumBySql(
            f"select sum(setting_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10 and t.service_key ='{serId}'")
        introduceOpenRate = getRateByXY(introduceOpenNum, introduceNum)
        settingOpenRate = getRateByXY(settingOpenNum, settingNum)
        serName = serIdToName(serId)
        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-{serName}-进入介绍页次数{introduceNum},进入介绍页并打开服务次数{introduceOpenNum},介绍页转化率{introduceOpenRate}," \
                        f"进入设置页次数{settingNum},进入设置页并打开服务次数{settingOpenNum},设置页转化率{settingOpenRate}"
            print(logDetail)
            # 插入早间服务转化率
            insertSql = f"""insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                            setting_open_num,setting_conver_rate,service_key,ref_date) 
                        value({introduceNum},{introduceOpenNum},{introduceOpenRate},{settingNum},{settingOpenNum},{settingOpenRate},'{serId.upper()}','{toMonthDay}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-{serName}-进入介绍页次数{introduceNum},进入介绍页并打开服务次数{introduceOpenNum},介绍页转化率{introduceOpenRate}," \
                        f"进入设置页次数{settingNum},进入设置页并打开服务次数{settingOpenNum},设置页转化率{settingOpenRate}"
            print(logDetail)
            # 更新早间服务订阅设备量
            updateSql = f"""
                        update analysis_conversion_rate set introduction_show_num = {introduceNum} ,introduction_open_num = {introduceOpenNum} ,introduction_conver_rate = {introduceOpenRate} 
                        ,setting_show_num = {settingNum} ,setting_open_num = {settingOpenNum} ,setting_conver_rate = {settingOpenRate} ,update_time = now()  where
                            ref_date = '{toMonthDay}' and service_key ='{serId.upper()}' 
                    """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='zjkj':
        serName = "早间开机"
    elif serId =='jkzx':
        serName = "健康作息"
    elif serId =='yjyl':
        serName = "夜间音量"
    elif serId =='zjtx':
        serName = "追剧提醒"
    else:
        serName = "其他"
    return serName

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 51
        jobName = '核心指标-生活助手-介绍页/设置页转化率'
        tableName = 'analysis_conversion_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    monthdate = getMonthdate(d)
    print (f"{date}日期,执行<主动服务转化率>报表")
    insertConversionRate(date,'zjkj')
    insertConversionRate(date,'yjyl')
    insertConversionRate(date,'jkzx')
    insertConversionRate(date,'zjtx')
    insertConversionRateMonth(monthdate,'zjkj')
    insertConversionRateMonth(monthdate,'yjyl')
    insertConversionRateMonth(monthdate,'jkzx')
    insertConversionRateMonth(monthdate,'zjtx')
